using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using UDS.Management.Data.Package;

namespace UDS.Management.Data.SchemaManagement
{
    /// <summary>
    /// Encapsulates versioning the database
    /// </summary>
    internal static class Schema
    {
        private static EmbeddedSqlScriptReader scriptReader = new EmbeddedSqlScriptReader();

		private const int SQL_ERROR_OBJECT_NOT_FOUND = 208;

		/// <summary>
		/// This version needs to change when there is a schema change with the tables that store
		/// the schema version information
		/// </summary>
		internal const string SCHEMA_MANAGEMENT_VERSION = "2.0";
		/// <summary>
		/// This version needs to change when there is a schema change to the manifest
		/// </summary>
		internal const string MANIFEST_VERSION = "1.0";

		private static void UpdateSchemaVersion(SqlTransaction transaction, System.Version libraryVersion, int timeOut)
		{
			SqlParameter VersionParam = new SqlParameter("@Version", System.Data.SqlDbType.VarChar);
			VersionParam.Value = libraryVersion.ToString();

            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.SetSchemaManagementVersion.sql", VersionParam);
            cmd.Connection = transaction.Connection;
            cmd.Transaction = transaction;
            cmd.CommandTimeout = timeOut;
            cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// Check that the major and minor portion of version 1 is not greater than
		/// the major and minor portion of version 2.
		/// </summary>
		/// <param name="version1">The version number to compare</param>
		/// <param name="version2">The version number to compare against</param>
		/// <returns>True if version 1 is not greater than version 2</returns>
		internal static bool CheckVersion(System.Version version1, System.Version version2)
		{
			if (version1 == null)
			{
				throw new System.ArgumentNullException("version1", "An invalid version was specified");
			}

			if (version2 == null)
			{
				throw new System.ArgumentNullException("version2", "An invalid version was specified");
			}		

			if (version1.Major > version2.Major)
			{
				return false;
			}
			else if (version1.Major == version2.Major
				&& version1.Minor > version2.Minor)
			{
				return false;
			}
			/*else if (version1.Major == version2.Major
				&& version1.Minor == version2.Minor
				&& version1.Build > version2.Build)
			{
				return false;
			}*/

			return true;
		}

		/// <summary>
		/// Get the version of the schema tables in the database currently.
		/// </summary>
		/// <param name="connection">The connection to use</param>
		/// <param name="timeOut">The timeout for the command</param>
		/// <returns></returns>
		private static System.Version GetCurrentSchemaManagementVersion(SqlTransaction transaction, int timeOut)
		{
            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.GetSchemaManagementVersion.sql");
            cmd.Connection = transaction.Connection;
            cmd.Transaction = transaction;
            cmd.CommandTimeout = timeOut;
            try
            {
                object VersionObject = cmd.ExecuteScalar();

                if (VersionObject != null)
                {
                    return new System.Version(Convert.ToString(VersionObject));
                }
                return null;
            }
            catch (SqlException e)
            {
                // If table does not exist, return null
                if (e.Number == SQL_ERROR_OBJECT_NOT_FOUND)
                {
                    return null;
                }
                throw;
            }
		}

		/// <summary>
		/// Return the version of the schema management library
		/// </summary>
		/// <returns>The version of the library</returns>
		private static System.Version GetSchemaManagementVersion()
		{
			return new System.Version(SCHEMA_MANAGEMENT_VERSION);
		}

		internal static void UpdateSchemaInstallStatus(SqlTransaction transaction, SchemaInstall install, int timeOut)
		{
			SqlParameter p1 = new SqlParameter("@InstallId", System.Data.SqlDbType.UniqueIdentifier);
			p1.Value = install.InstallId;

			SqlParameter p2 = new SqlParameter("@Status", System.Data.SqlDbType.VarChar);
			p2.Value = install.Status.ToString();

			SqlParameter p3 = new SqlParameter("@StatusText", System.Data.SqlDbType.VarChar);
            p3.Value = install.StatusText;

            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.UpdateSchemaInstallStatus.sql", new SqlParameter[] { p1, p2, p3 });
            cmd.Connection = transaction.Connection;
            cmd.Transaction = transaction;
            cmd.CommandTimeout = timeOut;
            cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// Apply the overall schema install information to the database
		/// </summary>
		/// <param name="transaction">The transaction to use</param>
		/// <param name="install">The schema install object to use</param>
		/// <param name="timeOut">The timeout value to use during this command</param>
		internal static void ApplySchemaInstall(SqlTransaction transaction, SchemaInstall install, int timeOut)
		{
			SqlParameter p1 = new SqlParameter("@InstallId", System.Data.SqlDbType.UniqueIdentifier);
			p1.Value = install.InstallId;

            SqlParameter p2 = new SqlParameter("@PackageId", System.Data.SqlDbType.UniqueIdentifier);
            p2.Value = install.PackageId;
            
            SqlParameter p3 = new SqlParameter("@Status", System.Data.SqlDbType.VarChar);
			p3.Value = install.Status.ToString();

			SqlParameter p4 = new SqlParameter("@StatusText", System.Data.SqlDbType.VarChar);
            p4.Value = install.StatusText;

			SqlParameter p5 = new SqlParameter("@InstalledBy", System.Data.SqlDbType.VarChar);
			p5.Value = install.InstalledBy;

			SqlParameter p6 = new SqlParameter("@PackageName", System.Data.SqlDbType.VarChar);
			p6.Value = install.PackageName;

			SqlParameter p7 = new SqlParameter("@PackageDescription", System.Data.SqlDbType.VarChar);
			p7.Value = install.PackageDescription;

            SqlParameter p8 = new SqlParameter("@InstallType", install.installType.ToString());
			
            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.ApplySchemaInstall.sql", new SqlParameter[] { p1, p2, p3, p4, p5, p6, p7, p8 });
            cmd.Connection = transaction.Connection;
            cmd.Transaction = transaction;
            cmd.CommandTimeout = timeOut;
            cmd.ExecuteNonQuery();
		}

        /// <summary>
        /// Get all the current applied changes in the system
        /// </summary>
        /// <param name="Connection">The Connection to use</param>
        /// <param name="TimeOut">The time out to use</param>
        /// <returns>A dictionary of all changes, hashed by id</returns>
        internal static Dictionary<Guid, SchemaChange> GetAppliedChanges(SqlConnection Connection, int TimeOut)
        {
            /* Make sure XBSchemaInstall objects exist, create if not there */
            Schema.CreateDatabaseVersionSchema(Connection, TimeOut);

            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.GetVersionChanges.sql");
            cmd.Connection = Connection;
            cmd.CommandTimeout = TimeOut;

            Dictionary<Guid, SchemaChange> ChangeList = new Dictionary<Guid, SchemaChange>();

            // Execute with the reader, and build up the return list
            using (SqlDataReader Reader = cmd.ExecuteReader())
            {
				/* Columns returned:
                     * SchemaChangeLogId, SchemaChangeDescription, SchemaFamilyId, FamilyName, 
					 * SchemaFamilyDescription, InstallId
					 */

				int SchemaChangeLogIdOrdinal = Reader.GetOrdinal("SchemaChangeLogId");
				int SchemaChangeDescriptionOrdinal = Reader.GetOrdinal("SchemaChangeDescription");
				int SchemaFamilyIdOrdinal = Reader.GetOrdinal("SchemaFamilyId");
				int FamilyNameOrdinal = Reader.GetOrdinal("FamilyName");
				int SchemaFamilyDescriptionOrdinal = Reader.GetOrdinal("SchemaFamilyDescription");
				int InstallIdOrdinal = Reader.GetOrdinal("InstallId");

                while (Reader.Read())
                {                    
                    SchemaChange c = new SchemaChange();
					c.ChangeId = Reader.GetGuid(SchemaChangeLogIdOrdinal);
					c.Description = GetSqlString(Reader, SchemaChangeDescriptionOrdinal);                   
					c.FamilyId = Reader.GetGuid(SchemaFamilyIdOrdinal);
                    c.FamilyName = GetSqlString(Reader, FamilyNameOrdinal);
					c.FamilyDescription = GetSqlString(Reader, SchemaFamilyDescriptionOrdinal);
					c.InstallId = Reader.GetGuid(InstallIdOrdinal);
                    ChangeList.Add(c.ChangeId, c);
                }
            }

            return ChangeList;
        }

		internal static SchemaInstall[] GetVersionHistory(SqlConnection connection, int timeOut)
		{
            /* Make sure XBSchemaInstall objects exist, create if not there */
            Schema.CreateDatabaseVersionSchema(connection, timeOut);

            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.GetVersionHistory.sql");
            cmd.Connection = connection;
            cmd.CommandTimeout = timeOut;

			List<SchemaInstall> InstallList = new List<SchemaInstall>();

			try
			{
				using (SqlDataReader Reader = cmd.ExecuteReader())
				{
					Guid LastInstallId = Guid.Empty;
					SchemaInstall LastInstall = null;

					List<SchemaChange> ChangeList = new List<SchemaChange>();

					/* Columns returned:
					 * SchemaChangeLogId, SchemaChangeDescription, Status, 
					 * StatusText, SchemaFamilyId, FamilyName, 
					 * SchemaFamilyDescription, PackageName, PackageDescription, 
					 * InstallDate, InstalledBy, InstallId
					 */

					int SchemaChangeLogIdOrdinal = Reader.GetOrdinal("SchemaChangeLogId");
					int SchemaChangeDescriptionOrdinal = Reader.GetOrdinal("SchemaChangeDescription");
					int StatusOrdinal = Reader.GetOrdinal("Status");
					int StatusTextOrdinal = Reader.GetOrdinal("StatusText");
					int SchemaFamilyIdOrdinal = Reader.GetOrdinal("SchemaFamilyId");
					int FamilyNameOrdinal = Reader.GetOrdinal("FamilyName");
					int SchemaFamilyDescriptionOrdinal = Reader.GetOrdinal("SchemaFamilyDescription");
					int PackageNameOrdinal = Reader.GetOrdinal("PackageName");
					int PackageDescriptionOrdinal = Reader.GetOrdinal("PackageDescription");
					int InstallDateOrdinal = Reader.GetOrdinal("InstallDate");
					int InstalledByOrdinal = Reader.GetOrdinal("InstalledBy");
					int InstallIdOrdinal = Reader.GetOrdinal("InstallId");

					while (Reader.Read())
					{
						Guid InstallId = Reader.GetGuid(InstallIdOrdinal);

						if (InstallId != LastInstallId)
						{
							if (ChangeList.Count > 0)
							{
								LastInstall.Changes = ChangeList.ToArray();
								ChangeList.Clear();
							}

							// Create a new install
							SchemaInstall Install = new SchemaInstall();
							Install.InstallDate = Reader.GetDateTime(InstallDateOrdinal);
							Install.InstalledBy = GetSqlString(Reader, InstalledByOrdinal);
							Install.InstallId = InstallId;
							Install.PackageDescription = GetSqlString(Reader, PackageDescriptionOrdinal);
							Install.PackageName = GetSqlString(Reader, PackageNameOrdinal);
							Install.Status = (InstallStatus)Enum.Parse(typeof(InstallStatus), GetSqlString(Reader, StatusOrdinal));
							Install.StatusText = GetSqlString(Reader, StatusTextOrdinal);
							LastInstall = Install;
							InstallList.Add(Install);
						}

						LastInstallId = InstallId;

						if (!Reader.IsDBNull(0))
						{
							SchemaChange c = new SchemaChange();
							c.ChangeId = Reader.GetGuid(SchemaChangeLogIdOrdinal);
							c.Description = GetSqlString(Reader, SchemaChangeDescriptionOrdinal);
							c.FamilyId = Reader.GetGuid(SchemaFamilyIdOrdinal);
							c.FamilyName = GetSqlString(Reader, FamilyNameOrdinal);
							//c.FamilyDescription = GetSqlString(Reader, 4);
							c.InstallId = InstallId;
							ChangeList.Add(c);
						}
					}

					if (ChangeList.Count > 0 && LastInstall.Changes == null)
					{
						LastInstall.Changes = ChangeList.ToArray();
					}
				}				
			}
			catch (SqlException SqlException)
			{
				// Object doesn't exist error will be ignored, and null returned
				if (SqlException.Number != SQL_ERROR_OBJECT_NOT_FOUND)
				{
					throw;
				}
			}

			if (InstallList != null && InstallList.Count > 0)
			{
				return InstallList.ToArray();
			}
			else
			{
				return null;
			}
		}

        /// <summary>
        /// Apply the change to the database version list
        /// </summary>
        /// <param name="transaction">The transaction to use</param>
		/// <param name="change">The change to apply</param>
        /// <param name="timeOut">The command timeout</param>
        internal static void ApplySchemaChange(SqlTransaction transaction, SchemaChange change, int timeOut)
        {
			SqlParameter p1 = new SqlParameter("@ChangeId", System.Data.SqlDbType.UniqueIdentifier);
			p1.Value = change.ChangeId;

			SqlParameter p2 = new SqlParameter("@ChangeDescription", System.Data.SqlDbType.VarChar);
			p2.Value = change.Description;

			SqlParameter p3 = new SqlParameter("@FamilyId", System.Data.SqlDbType.UniqueIdentifier);
			p3.Value = change.FamilyId;

			SqlParameter p4 = new SqlParameter("@FamilyName", System.Data.SqlDbType.VarChar);
			p4.Value = change.FamilyName;

			SqlParameter p5 = new SqlParameter("@FamilyDescription", System.Data.SqlDbType.VarChar);
			p5.Value = change.FamilyDescription;

			SqlParameter p6 = new SqlParameter("@InstallId", System.Data.SqlDbType.UniqueIdentifier);
			p6.Value = change.InstallId;

            // Read the script with the parameters
            SqlCommand cmd = scriptReader.GetScriptCommand("UDS.Management.Data.Sql.ApplySchemaChange.sql", new SqlParameter[] { p1, p2, p3, p4, p5, p6 });
            cmd.Connection = transaction.Connection;
            cmd.Transaction = transaction;
            cmd.CommandTimeout = timeOut;
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Create the database version schema.  If the schemas already
        /// exists, this is a no-op.  This will be run on a transaction
        /// and rolled back if the creation fails.
        /// </summary>
		/// <exception cref="Exceptions.DatabaseManagementException">Thrown if the versions are incompatible</exception>
        /// <param name="Connection">The Connection to execute on</param>
        /// <param name="TimeOut">The time out value to use</param>
        internal static void CreateDatabaseVersionSchema(SqlConnection Connection, int TimeOut)
        {
            if (Connection.State != System.Data.ConnectionState.Open)
            {
                Connection.Open();
            }

            using (SqlTransaction Transaction = Connection.BeginTransaction())
            {
                try
                {
					// Ensure that the version is correct
					System.Version LibraryVersion = GetSchemaManagementVersion();
					System.Version DatabaseVersion = GetCurrentSchemaManagementVersion(Transaction, TimeOut);

					// Make sure the current database version is not greater than the library version
					if (DatabaseVersion != null && !CheckVersion(DatabaseVersion, LibraryVersion))
					{
						// Throw an exception
						throw new Exceptions.DatabaseManagementException(
							String.Format("Database schema management version {0} is greater than the library version {1}.  Please update the database management library to a compatible version",
							DatabaseVersion.ToString(),
							LibraryVersion.ToString())
						);
					}

                    // Create XBSchema schema objects
                    using (SqlSource script = scriptReader.GetSqlSource("UDS.Management.Data.Sql.XBSchema.sql"))
                    {
                        SqlHelper.ExecuteContent(script, Transaction, TimeOut);
                    }

                    // Update the version
					UpdateSchemaVersion(Transaction, LibraryVersion, TimeOut);
					
                    Transaction.Commit();
                }
                catch 
                {
                    try
                    {
                        Transaction.Rollback();
                    }
                    catch
                    {
                        // Ignore
                    }
                    throw;
                }
            } 
        }

        /// <summary>
        /// Create the database replication metadata schema.  If the schemas already
        /// exists, this is a no-op.  This will be run on a transaction
        /// and rolled back if the creation fails.
        /// </summary>
        /// <param name="Connection">The Connection to execute on</param>
        /// <param name="TimeOut">The time out value to use</param>
        internal static void CreateReplicationMetaDataSchema(SqlConnection Connection, int TimeOut)
        {
            using (SqlTransaction Transaction = Connection.BeginTransaction())
            {
                try
                {
                    using (SqlSource script = scriptReader.GetSqlSource("UDS.Management.Data.Sql.CreatePublicationTables.sql"))
                    {
                        SqlHelper.ExecuteContent(script, Transaction, TimeOut);
                    }

                    Transaction.Commit();
                }
                catch
                {
                    try
                    {
                        Transaction.Rollback();
                    }
                    catch (SqlException sqlEx)
                    {
                        throw new Exceptions.SqlScriptException(String.Format("Failed to Create Publication Tables on the database with Exception : {0}", sqlEx.Message));
                    }
                    throw;
                }
            }
        }

        /// <summary>
        /// Create the database SQL Jobs metadata schema.  If the schemas already
        /// exists, this is a no-op.  This will be run on a transaction
        /// and rolled back if the creation fails.
        /// </summary>
        /// <param name="Connection">The Connection to execute on</param>
        /// <param name="TimeOut">The time out value to use</param>
        internal static void CreateSQLJobsMetaDataSchema(SqlConnection Connection, int TimeOut)
        {
            using (SqlTransaction Transaction = Connection.BeginTransaction())
            {
                try
                {
                    // Make sure the management version table is created
                    using (SqlSource script = scriptReader.GetSqlSource("UDS.Management.Data.Sql.CreateSQLJobsTables.sql"))
                    {
                        SqlHelper.ExecuteContent(script, Transaction, TimeOut);
                    }
                    Transaction.Commit();
                }
                catch
                {
                    try
                    {
                        Transaction.Rollback();
                    }
                    catch (SqlException sqlEx)
                    {
                        throw new Exceptions.SqlScriptException(String.Format("Failed to Create SQL Job Tables on the database with Exception : {0}", sqlEx.Message));
                    }
                    throw;
                }
            }
        }

        /// <summary>
		/// Helper function that will avoid the null exception that GetString causes
		/// if the value is null.  Read a SqlString, check if it is null, and return
		/// a null if it is.
		/// </summary>
		/// <param name="reader">The reader to read the string from</param>
		/// <param name="field">The field to read at</param>
		/// <returns>Returns null if the value at the field specified is sql null</returns>
		private static string GetSqlString(SqlDataReader reader, int field)
		{
			System.Data.SqlTypes.SqlString SqlString = reader.GetSqlString(field);
			if (!SqlString.IsNull)
			{
				return SqlString.Value;
			}
			else
			{
				return null;
			}
		}
    }
}
